package net.codesree.dao;

import java.io.Serializable; 
import java.util.List;
import java.util.Date;
import java.util.ArrayList;

import java.sql.Types;
import java.sql.ResultSet;
import java.sql.Connection;
import java.sql.Statement;
import java.sql.SQLException;
import java.sql.PreparedStatement;
import java.sql.CallableStatement;

import javax.sql.DataSource;
import net.codesree.dao.IRowMapper;
import net.codesree.dao.DAOException;

public class JDBCTemplate implements Serializable {

 private DataSource dSource=null;
 private Connection con=null;
 private ResultSet rs=null;
 private PreparedStatement ps=null;
 private CallableStatement cs=null;
 private boolean isConnected=false;
 
 public JDBCTemplate(DataSource _dSource) {
  this.dSource=_dSource;
 }
 
 public int insertStatement(String queryStr,Object[] params,boolean retGenKey) throws DAOException {
  int retIntValue=-1;
  try {
	initialisePs(queryStr,params,retGenKey);
	retIntValue=ps.executeUpdate();
	if(retGenKey) {
	 rs=ps.getGeneratedKeys();
	 while(rs.next()) {
	  retIntValue=rs.getInt(1);
	 }
	 rs.close();
   }
  } catch(SQLException sqlex) {
	throw new DAOException("Data Access Exception: "+sqlex.getMessage(),sqlex);
  } finally {
	close(rs,ps,con);  
  }
  return retIntValue;
 }
 
 public int updateStatement(String queryStr,Object[] params) throws DAOException {
  return insertStatement(queryStr,params,false);
 }
 
 public void callStatement(String queryStr,Object[] inParams,Object[] outParams) throws DAOException {
  try {
	initialiseCs(queryStr,inParams,outParams);
    cs.execute();	
  } catch(SQLException sqlex) {
	throw new DAOException("Data Access Exception: "+sqlex.getMessage(),sqlex);  
  } finally {
	close(cs,con);  
  }
 }
 
 public Object queryForObject(String queryStr,IRowMapper mapper,Object[] params) throws DAOException {
  Object retObject=null;
  try {
   initialisePs(queryStr,params,false);
   this.ps.executeQuery();
   if(this.rs!=null && this.rs.next()) { this.rs.first(); }
   retObject=mapper.mapResultToBean(this.rs,1);
  } catch(SQLException sqlex) {
    throw new DAOException("Data Access Exception: "+sqlex.getMessage(),sqlex);
  } finally {
    close(rs,ps,con);	  
  }
  return retObject;
 }
 
 public List<Object> queryForObjects(String queryStr,IRowMapper mapper,Object[] params) throws DAOException {
  List<Object> resultList=null;
  int rowNum=1;
  try {
	initialisePs(queryStr,params,false);
	this.rs=this.ps.executeQuery();
	if(rs!=null && rs.next()) {
     rs.last();
     if(rs.getRow()>1)
 	  resultList=new ArrayList<Object>();
     rs.first();
    }
    while(rs.next()) {	
     resultList.add(mapper.mapResultToBean(rs,rowNum));
     rowNum+=1;	
    }
  } catch (SQLException sqlex) {
	throw new DAOException("Data Access Exception: "+sqlex.getMessage(),sqlex);
  } finally {
	close(rs,ps,con);
  }
  return resultList;
 }
 
 public long queryForLong(String queryStr,Object[] params) throws DAOException {
  long retVal=Long.parseLong("100");
  try {
	initialisePs(queryStr,params,false);
	this.rs=this.ps.executeQuery();
	IRowMapper longMapper=new IRowMapper() {
     public Object mapResultToBean(ResultSet resultSet,int rowNum) throws SQLException {
	   if(rowNum<=0)
	    resultSet.absolute(1);
      return resultSet.getLong(1);
     }
    };
	this.rs=this.ps.executeQuery();
	retVal=Long.parseLong((String) longMapper.mapResultToBean(this.rs,1));
  } catch(SQLException sqlex) {
	throw new DAOException("Data Access Exception: "+sqlex.getMessage(),sqlex);
  } finally {
	close(this.rs,this.ps,this.con);
  }
  return retVal;
 }
 
 public int queryForInt(String queryStr,Object[] params) throws DAOException {
  int retVal=-1;
  try {
	initialisePs(queryStr,params,false);
	IRowMapper intMapper = new IRowMapper() {
	 public Object mapResultToBean(ResultSet resultSet,int rowNum) throws SQLException {
	  if(rowNum<=0)
	   resultSet.absolute(1);
	  return resultSet.getInt(1);  
	 }
    };
	this.rs=this.ps.executeQuery();
	retVal=Integer.parseInt((String) intMapper.mapResultToBean(this.rs,1));
  } catch(SQLException sqlex) {
	throw new DAOException("Data Access Exception: "+sqlex.getMessage(),sqlex);
  } finally {
	close(this.rs,this.ps,this.con);
  }
  return retVal;
 }
 
 public String queryForString(String queryStr,Object[] params) throws DAOException {
  String retVal=null;
  try {
	initialisePs(queryStr,params,false);
	IRowMapper stringMapper = new IRowMapper() {
	 public Object mapResultToBean(ResultSet resultSet,int rowNum) throws SQLException {
	  if(rowNum<=0)
	   resultSet.absolute(1);
	  return resultSet.getInt(1);  
	 }
    };
	this.rs=this.ps.executeQuery();
	retVal=(String) stringMapper.mapResultToBean(this.rs,1);
  } catch(SQLException sqlex) {
	throw new DAOException("Data Access Exception: "+sqlex.getMessage(),sqlex);
  } finally {
	close(this.rs,this.ps,this.con);
  }
  return retVal;
 }
  
 private void prepareStatement(String queryStr,Object[] params,boolean retGenKeys) throws SQLException {
  if(retGenKeys)
	ps=con.prepareStatement(queryStr,Statement.RETURN_GENERATED_KEYS);
  else
   ps=con.prepareStatement(queryStr);
  for(int i=0;i<params.length;i++) {
	Object obj=params[i];
	int sqlType=getParamType(obj);
	if(sqlType==Types.INTEGER) {
	  ps.setInt(i,((Integer) obj).intValue());
    } else if(sqlType==Types.DATE) {
	  ps.setDate(i,new java.sql.Date(((java.util.Date) obj).getTime()));
    } else if(sqlType==Types.BOOLEAN) {
	  ps.setBoolean(i,((Boolean) obj).booleanValue());
    } else if(sqlType==Types.VARCHAR) {
	  ps.setString(i,(String) obj);
    } else if (sqlType==Types.LONGVARCHAR) {
	  ps.setLong(i,((Long) obj).longValue());
    } else if(sqlType==Types.FLOAT) {
	  ps.setFloat(i,((Float) obj).floatValue());
    } else if(sqlType==Types.DOUBLE) {
	  ps.setDouble(i,((Double) obj).doubleValue());
    } else {
	  ps.setObject(i,obj);
	}
  }
 }
 
 private void getConnection() throws SQLException {
   if(this.con.isClosed()==true && this.dSource!=null) {
	this.con=this.dSource.getConnection();  
    this.con.setAutoCommit(false);
  }
 }
 
 private void close(ResultSet _rs,PreparedStatement _ps,Connection _con) {
  try {
	if(_rs!=null)
	  _rs.close();
    if(_ps!=null)
	  _ps.clearWarnings();
	  _ps.clearParameters();
	  _ps.close();
	if(_con!=null && _con.isClosed()==false)
	  _con.setAutoCommit(true);
	  _con.close();
  } catch(SQLException sqlex) {
	System.err.println("Exception while releasing resources: "+sqlex.getMessage());
  }
 }
 
 private void close(CallableStatement _cs,Connection _con) {
  try {
    if(_cs!=null)
	  _cs.clearWarnings();
	  _cs.clearParameters();
	  _cs.close();
	if(_con!=null && _con.isClosed()==false)
	  _con.setAutoCommit(true);
	  _con.close();
  } catch(SQLException sqlex) {
	System.err.println("Exception while releasing resources: "+sqlex.getMessage());
  }
 }
 
 private void initialisePs(String queryStr,Object[] params,boolean retGenKey) throws SQLException {
   getConnection();
   prepareStatement(queryStr,params,retGenKey);
 }

 private void initialiseCs(String queryStr,Object[] inParams,Object[] outParams) throws SQLException {
  getConnection();
  prepareCall(queryStr,inParams,outParams);
 }
 
 private void initialiseCs(String queryStr,Object[] inParams) throws SQLException {
  getConnection();
  prepareCall(queryStr,inParams,null);
 }
 
  private void prepareCall(String queryStr,Object[] inParams,Object[] outParams) throws SQLException {
   getConnection();
   this.cs=this.con.prepareCall("CALL "+queryStr);
   for(int i=0;i<inParams.length;i++) {
	Object obj=inParams[i];
	int sqlType=getParamType(obj);
	if(sqlType==Types.INTEGER) {
	  this.cs.setInt(i,((Integer) obj).intValue());
    } else if(sqlType==Types.DATE) {
	  this.cs.setDate(i,new java.sql.Date(((java.util.Date) obj).getTime()));
    } else if(sqlType==Types.BOOLEAN) {
	  this.cs.setBoolean(i,((Boolean) obj).booleanValue());
    } else if(sqlType==Types.VARCHAR) {
	  this.cs.setString(i,(String) obj);
    } else if (sqlType==Types.LONGVARCHAR) {
	  this.cs.setLong(i,((Long) obj).longValue());
    } else if(sqlType==Types.FLOAT) {
	  this.cs.setFloat(i,((Float) obj).floatValue());
    } else if(sqlType==Types.DOUBLE) {
	  this.cs.setDouble(i,((Double) obj).doubleValue());
    } else {
	  this.cs.setObject(i,obj);
	}
  }
   if(outParams!=null && outParams.length<=1) {
    for(int i=1;i<outParams.length;i++) {	   
	 this.cs.registerOutParameter(i,getParamType(outParams[i]));
    }
   }
  }
  
  private int getParamType(Object val) {
	int paramType=-1;
	if(val instanceof Integer) {
	  paramType=Types.INTEGER;
    } else if(val instanceof java.util.Date) {
	  paramType=Types.DATE;
    } else if(val instanceof Boolean) {
	  paramType=Types.BOOLEAN;
    } else if(val instanceof String) {
	  paramType=Types.VARCHAR;
    } else if (val instanceof Long) {
      paramType=Types.LONGVARCHAR;
    } else if(val instanceof Float) {
	  paramType=Types.FLOAT;
    } else if(val instanceof Double) {
	  paramType=Types.DOUBLE;
    }
	return paramType;
  }
 }

 
